let mysql = require("mysql");
let connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : 'root',
    database : 'bookshop'
});

connection.connect();

const {pagerHelper} = require("../../util/PagerHelper");

function toPromise(sql, callback) {
    return new Promise(((resolve, reject) => {
        connection.query(sql, (err, results) => {
            if (err) {
                reject(err);
            }else {
                console.log("results", results);
                if (callback) callback(results, resolve);
                else resolve(results);
            }
        });
    }))
}

/**
 * 同步函数，分页查询书本
 * @param where 查询条件对象
 * @param columns   查询的字段名称
 */
// Promise
async function findByPage(where) {
    return toPromise(`select * from books ${where} limit ${(pagerHelper.pageNum - 1) * pagerHelper.pageSize}, ${pagerHelper.pageSize}`);
}

/**
 * 根据id搜索
 * @param _id
 * @returns {Promise<*>}
 */
async function findById(id) {
    return toPromise(`select * from books where id = ${id}`, (results, resolve) => {
        resolve(results ? results[0] : null);
    });
}

/**
 * 查询数据总数
 * @param sql
 * @returns {Promise<*>}
 */
async function getCount(where) {
    return toPromise(`select count(1) as count from books ${where}`, (results, resolve) => {
        resolve(results[0].count);
    });
}

/**
 * 根据id删除数据
 * @param _id
 * @returns {Promise<*>}
 */
async function deleteById(id) {
    return toPromise(`delete from books where id = ${id}`);
}

/**
 * 删除多个ids的数据
 * @param ids
 * @returns {Promise<*>}
 */
async function deleteByIds(ids) {
    if (Array.isArray(ids)) {
        let str = "";
        ids.forEach(value => {
            str += "'" + value + "',";
        })
        str = str.substring(0, str.length - 1);
        return toPromise(`delete from books where id in (${str})`);
    }
}

/**
 * 插入数据到数据库中
 * @param obj
 * @returns {Promise<EnforceDocument<unknown, {}>[]>}
 */
async function insert(obj) {
    // return toPromise(`insert into books(id, name, price, count, createDate) value(0, ${obj.name}, ${obj.price}, ${obj.count}, ${obj.createDate})`);
    return toPromise(getInsertSqlFromObj(obj, "books"));
}

function getInsertSqlFromObj(obj, table) {
    let sql = `insert into ${table}(`;
    for (let key of Object.keys(obj)) {
        sql += key + ",";
    }
    sql = sql.substring(0, sql.length - 1);
    sql += ") values(";
    for (let val of Object.values(obj)) {
        sql += "'" + val + "',";
    }
    sql = sql.substring(0, sql.length - 1);
    sql += ");";
    return sql;
}

function getUpdateSqlFromObj(obj, table) {
    let sql = `update ${table} set `;
    for (let [key, val] of Object.entries(obj)) {
        if (key == "id") continue;
        sql += `${key} = '${val}', `;
    }
    sql = sql.substring(0, sql.lastIndexOf(","));
    sql += " where id = " + obj.id;
    return sql;
}

/**
 * 编辑书本
 * @param obj
 * @param id
 * @returns {Promise<*>}
 */
async function update(obj, id) {
    return toPromise(getUpdateSqlFromObj(obj, "books"));
}

/**
 * 无条件获取所有数据
 * @returns {Promise<*>}
 */
// async function findAll() {
//     return await model.find();
// }

module.exports = {
    findByPage, findById, getCount, deleteById, deleteByIds, insert, update//, findAll
}